<?php
/**
* @file $Id: ProgressReports.php 476 2007-04-27 05:12:08Z focus-sis $
* @package Focus/SIS
* @copyright Copyright (C) 2006 OASIS Project. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* Focus/SIS is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
*/

/*Based on a marking_period_id
 * produce up to date report with "final" grades as of now
 * include things due before today, not on today (becuase todays assignements might not be entered yet)
 * each student gets one line per period with grade
 */

// get a list of psudeo constants for this time throught the page
$db_date = DbDate();
$user_school = UserSchool();
$user_syear = UserSyear();
$mp_list = GetAllMP('QTR', GetCurrentMP('QTR', $db_date));
$user_mp = UserMP();
$ODD_ROW_COLOR = "#F8F8F9";
$EVEN_ROW_COLOR = "#D0D0FF";

$mp_RET = DBGet(DBQuery("
		SELECT       mp.START_DATE
		 ,           mp.END_DATE
		 ,           mp.SYEAR
		FROM         SCHOOL_QUARTERS mp
		WHERE        mp.MARKING_PERIOD_ID = '$user_mp'
	UNION
		SELECT       mp.START_DATE
		 ,           mp.END_DATE
		 ,           mp.SYEAR
		FROM         SCHOOL_YEARS mp
		WHERE        mp.MARKING_PERIOD_ID = '$user_mp'
	UNION
		SELECT       mp.START_DATE
		 ,           mp.END_DATE
		 ,           mp.SYEAR
		FROM         SCHOOL_SEMESTERS mp
		WHERE        mp.MARKING_PERIOD_ID = '$user_mp'
	UNION
		SELECT       mp.START_DATE
		 ,           mp.END_DATE
		 ,           mp.SYEAR
		FROM         SCHOOL_PROGRESS_PERIODS mp
		WHERE        mp.MARKING_PERIOD_ID = '$user_mp'
		"));

$mp_start = $mp_RET[1]['START_DATE'];
$mp_end = $mp_RET[1]['END_DATE'];
$mp_syear = $mp_RET[1]['SYEAR'];
unset($mp_RET);

//make an array that is a quick lookup table for GRADE_LETTER => GPA_VALUE for this school
$gpa_RET = DBGet(DBQuery("
		SELECT       rcg.TITLE as GRADE_LETTER
		 ,           rcg.GPA_VALUE
		FROM         REPORT_CARD_GRADES rcg
		WHERE        rcg.SCHOOL_ID = '$user_school'
		"));

foreach ($gpa_RET as $gpa_ROW) {
	$gpa_val[$gpa_ROW['GRADE_LETTER']] = $gpa_ROW['GPA_VALUE'];
}		

unset($gpa_RET);


// this is focus "magic" that I still dont understand - BL
$extra['force_search'] = true;
$extra['action'] .= "&_FOCUS_PDF=true";
Widgets('mailing_labels');


if (!$_REQUEST['search_modfunc'] 
||  $_REQUEST['search_modfunc'] == 'search' 
||  $_FOCUS['modules_search']
){	
	//  This builds the query form that lets you select students before it runs the report
	DrawHeader(_('Gradebook') . ' - ' . ProgramTitle());
	$extra['new'] = true;
	Search('student_id',$extra);
}
else {
	// query up a big list of everyone and thier associated classes
	$students_RET = DBGet(DBQuery("
			SELECT       stu.STUDENT_ID
			 ,           stu.LAST_NAME || ', ' || stu.FIRST_NAME as FULL_NAME
			 ,           grd.TITLE as GRADE_ID
			 ,           sp.TITLE as PERIOD_NAME
			 ,           crs.TITLE as COURSE_NAME
			 ,           usr.FIRST_NAME || ' ' || usr.LAST_NAME as TEACHER_NAME
			 ,           cp.COURSE_PERIOD_ID
			 ,           cp.GRADE_SCALE_ID
			 ,           crs.COURSE_ID
			 ,           usr.STAFF_ID
			 ,           usr.USERNAME
			 ,           cfg.VALUE as IS_WEIGHTED
			FROM         STUDENT_ENROLLMENT enr
			 JOIN        SCHOOL_GRADELEVELS grd
			  ON         enr.GRADE_ID = grd.ID
			 JOIN        STUDENTS stu
			  ON         stu.STUDENT_ID = enr.STUDENT_ID
			 JOIN        SCHEDULE sch
			  ON         sch.STUDENT_ID = enr.STUDENT_ID
			 JOIN        COURSE_PERIODS cp
			  ON         sch.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID
			 JOIN        COURSES crs
			  ON         sch.COURSE_ID = crs.COURSE_ID
			 JOIN        SCHOOL_PERIODS sp
			  ON         cp.PERIOD_ID = sp.PERIOD_ID
			 JOIN        USERS usr
			  ON         usr.STAFF_ID = cp.TEACHER_ID
			 LEFT JOIN   PROGRAM_USER_CONFIG cfg
			  ON         cfg.USERNAME = usr.USERNAME
			   AND       cfg.PROGRAM = 'Gradebook'
			   AND       cfg.TITLE = 'WEIGHT'
			WHERE        enr.SYEAR = '$mp_syear'
			 AND         sch.MARKING_PERIOD_ID in ($mp_list)
			 AND  (      sch.END_DATE is NULL 
			  OR         sch.END_DATE >= '$db_date' )
			 AND         cp.SCHOOL_ID = '$user_school'
			ORDER BY     stu.LAST_NAME
			 ,           stu.FIRST_NAME
			 ,           stu.STUDENT_ID
			 ,           sp.SORT_ORDER
			"));
	
	if (count($students_RET)) {
		// PDF stuff
		$handle = PDFStart();

		// in filtered versions of the same report, this should be false by defalt, and changed when appropriate		
		$include_student_by_default = false;

		// sane values for the first person, they get changed as we move to the next person
		$last_student_id = '';
		$credit_count = 0;
		$gpa_sum = 0.0;
		$student_html = '';
		$include_student = $include_student_by_default; 

		foreach ($students_RET as $student_ROW) {		    
			$student_id = $student_ROW['STUDENT_ID'];
			
			//new page for each student
			if ($student_id != $last_student_id) {
				$bg_color = '';
				unset($_FOCUS['DrawHeader']);
				if ($_REQUEST['mailing_labels'] == 'Y') {
					$student_html .= '
					<br />
					<br />
					<br />';
				}
					
				if ($last_student_id != "") {
				    // Show their gpa
					$student_html .= ' 
						<tr>
							<td>&nbsp;</td>
						</tr>
						<tr>
							<td>&nbsp;</td>
							<td colspan="7" align="center" nowrap>
								<strong><em>
									Projected GPA for current marking period: 
								</em></strong>
							</td>
							<td nowrap>
								'.number_format($gpa_sum / $credit_count, 2).'
							</td>
						</tr>';
							
					// now close up the page for this student
					$student_html .= ' 
					</table>
				<!-- NEW PAGE -->
					';
					
					if ($include_student) {
						echo $student_html;
					}

					$credit_count = 0;
					$gpa_sum = 0.0;
					$student_html = '';
					$include_student = $include_student_by_default;
				}				

				$student_html .= 
						  ComposeHeader(Config('TITLE') . ' ' . _('Progress Report - All Classes'))
						. ComposeHeader($student_ROW['FULL_NAME'], $student_id)
						. ComposeHeader($student_ROW['GRADE_ID'], GetSchool($user_school))
						. ComposeHeader(ProperDate($db_date), GetMP($current_marking_period));

				if ($_REQUEST['mailing_labels'] == 'Y') {
					$student_html .= ' 
					<br />
					<br />
					<table width="100%">
						<tr>
							<td width="50px">&nbsp;</td>
							<td>' . $student_ROW['MAILING_LABEL'] . '</td>
						</tr>
					</table>
					<br />';
				}
				
				$student_html .= '
					<br />
					<br />
					<br />
					<table>
						<tr bgcolor="#333366">
							<th width="3%">&nbsp;</th>
							<th align="left" width="10%">
								<b><font color="#FFFFFF">Period</font></b>
							</th>
							<th width="10%">&nbsp;</th>
							<th align="left" width="40%">
								<b><font color="#FFFFFF">Course</font></b>
							</th>
							<th width="10%">&nbsp;</th>
							<th align="left" width="10%">
								<b><font color="#FFFFFF">Teacher</font></b>
							</th>
							<th width="10%">&nbsp;</th>
							<th width="20%" colspan="3">
								<b><font color="#FFFFFF">Grade</font></b>
							</th>
							<th width="8%">&nbsp;</th>
						</tr>
						';
				
				$last_student_id = $student_id;
			}
			
			$sum_points = 0;
			$nonweighted_total_points = 0;
			$nonweighted_student_points = 0;
			$student_points = $total_points = array();
			$course_period_id = $student_ROW['COURSE_PERIOD_ID'];
			$grade_scale_id = $student_ROW['GRADE_SCALE_ID'];
			$course_id = $student_ROW['COURSE_ID'];
			$staff_id = $student_ROW['STAFF_ID'];
			$teacher_username = $student_ROW['USERNAME'];
			$is_weighted = $student_ROW['IS_WEIGHTED'];
			
			if ($bg_color == $ODD_ROW_COLOR) {
				$bg_color = $EVEN_ROW_COLOR;
			}
			else {
				$bg_color = $ODD_ROW_COLOR;
			}

			// ASSIGNMENT TYPES
			$types_RET = DBGet(DBQuery("
					SELECT       gat.ASSIGNMENT_TYPE_ID
					 ,           gat.TITLE
					 ,           gat.FINAL_GRADE_PERCENT
					FROM         GRADEBOOK_ASSIGNMENT_TYPES gat
					WHERE        gat.STAFF_ID = '$staff_id'
					 AND         gat.COURSE_ID = '$course_id'
					ORDER BY     gat.TITLE
					"));
			
			// select the grades for the assigments with proper weighting info
			if ($is_weighted) { 
				$select_grades_sql = "
						SELECT       a.TITLE
						 ,           a.ASSIGNED_DATE
						 ,           t.ASSIGNMENT_TYPE_ID
						 ,           a.ASSIGNMENT_TYPE_ID AS TYPE_ID
						 ,           t.FINAL_GRADE_PERCENT
						 ,           g.POINTS
						 ,           CASE 
						               WHEN g.POINTS = -1 THEN 0 
									   ELSE                    a.POINTS 
									   END AS TOTAL_POINTS
						 ,           g.COMMENT
						 ,           '' AS LETTER_GRADE
						 ,           a.COURSE_PERIOD_ID
						FROM         GRADEBOOK_ASSIGNMENTS a
						 LEFT JOIN   GRADEBOOK_ASSIGNMENT_TYPES t
						  ON         a.ASSIGNMENT_TYPE_ID = t.ASSIGNMENT_TYPE_ID
						 LEFT JOIN   GRADEBOOK_GRADES g
						  ON         a.ASSIGNMENT_ID = g.ASSIGNMENT_ID
						   AND       g.STUDENT_ID = '$student_id'
						WHERE        a.COURSE_PERIOD_ID = '$course_period_id'
						 OR (        a.COURSE_ID = '$course_id'
						  AND        a.STAFF_ID = '$staff_id' )
						";
			}
			else {
				$select_grades_sql = "
						SELECT       a.TITLE
						 ,           a.ASSIGNED_DATE
						 ,           '-1' AS ASSIGNMENT_TYPE_ID
						 ,           a.ASSIGNMENT_TYPE_ID AS TYPE_ID
						 ,           '1' AS FINAL_GRADE_PERCENT
						 ,           g.POINTS
						 ,           CASE 
						               WHEN g.POINTS = -1 THEN 0 
									   ELSE                    a.POINTS 
									   END AS TOTAL_POINTS
						 ,           g.COMMENT
						 ,           '' AS LETTER_GRADE
						 ,           a.COURSE_PERIOD_ID
						FROM         GRADEBOOK_ASSIGNMENTS a
						 LEFT JOIN   GRADEBOOK_GRADES g
						  ON         a.ASSIGNMENT_ID = g.ASSIGNMENT_ID
						   AND       g.STUDENT_ID = '$student_id'
						WHERE        a.COURSE_PERIOD_ID = '$course_period_id'
						 OR (        a.COURSE_ID = '$course_id'
						  AND        a.STAFF_ID = '$staff_id' )
						";
			}
			
			$grades_RET = DBGet(DBQuery($select_grades_sql)	,array(
					  'ASSIGNED_DATE' => '_removeSpaces'
					, 'TITLE'         => '_removeSpaces'
					, 'POINTS'        => '_makeExtra'
					, 'LETTER_GRADE'  => '_makeExtra')
					);
					
			foreach ($student_points as $assignment_type_id => $points) {
				$sum_points += $points / $total_points[$assignment_type_id];
			}

			foreach ($types_RET as $type_ROW) {
				if ($is_weighted !== 'Y') {
					$type_ROW['FINAL_GRADE_PERCENT'] = 1;
				}
			}
						
			$student_html .= '  
						<tr bgcolor="' . $bg_color . '">
							<td>&nbsp;</td>
							<td colspan="2" nowrap>
								' . $student_ROW['PERIOD_NAME'] . '
							</td>
							<td colspan="2" nowrap>
								' . $student_ROW['COURSE_NAME'] . '
							</td>
							<td colspan="2" nowrap>
								' . $student_ROW['TEACHER_NAME'] . '
							</td>';

			if ($is_weighted !== 'Y') {
				$percent = $nonweighted_student_points / $nonweighted_total_points;
				if ($percent > 0) {
				    $letter_grade = _makeLetterGrade(
							  $percent
							, $course_period_id
							, $teacher_username
							, $grade_scale_id 
							);

					switch ($letter_grade) {
						case 'D':
						case 'D+':
						case 'D-':
						case 'F': {
							$include_student = true;
						} 	break;
					}
							
					$gpa_sum += $gpa_val[$letter_grade];
					$credit_count++;
					$student_html .= '
							<td align="right">&nbsp;
								' . Percent($percent, 0) . '
							</td>
							<td align="left">&nbsp;
								' . $letter_grade . '
							</td>
							<td aligh="right">
								' . number_format($gpa_val[$letter_grade], 1) . '
							</td>';
				}
				else {
					$student_html .= '
							<td align="right">-</td>
							<td align="left">-</td>';
				}
			}
			else {
				if ($sum_points > 0) {
				    $letter_grade = _makeLetterGrade(
							  $sum_points
							, $course_period_id
							, $teacher_username
							, $grade_scale_id 
							);

					switch ($letter_grade) {
						case 'D':
						case 'D+':
						case 'D-':
						case 'F': {
							$include_student = true;
						} 	break;
					}

					$gpa_sum += $gpa_val[$letter_grade];
					$credit_count++;
					$student_html .= '
							<td align="right">&nbsp;
								' . Percent($sum_points, 0) . '
							</td>
							<td align="left">&nbsp;
								' . $letter_grade . '
							</td>
							<td aligh="right">
								' . number_format($gpa_val[$letter_grade], 1) . '
							</td>';
				}
				else {
					$student_html .= '
							<td align="right">-</td>
							<td align="left">-</td>';
				}
			}
			echo '
						</tr>';
			
			// Give some details about attendance for this class
			$start_date = $student_ROW['START_DATE'];
			$end_date = $student_ROW['END_DATE'];
			$syear = $student_ROW['SYEAR'];
					  
			$attendance_RET = DBGet(DBQuery("
					SELECT       ac.ID
					 ,           ac.TITLE
					 ,           ac.SHORT_NAME
					 ,           ac.STATE_CODE
					 ,           ( SELECT  count(*)
								   FROM    ATTENDANCE_PERIOD ap
								   WHERE   ac.ID = ap.ATTENDANCE_CODE
								    AND    ap.COURSE_PERIOD_ID = '$course_period_id'
								    AND    ap.STUDENT_ID = '$student_id'
								    AND    ap.SCHOOL_DATE 
									          BETWEEN '$mp_start' and '$mp_end'
							     ) as COUNT
					FROM         ATTENDANCE_CODES ac
					WHERE        ( SELECT  count(*)
								   FROM    ATTENDANCE_PERIOD ap
								   WHERE   ac.ID = ap.ATTENDANCE_CODE
								    AND    ap.COURSE_PERIOD_ID = '$course_period_id'
								    AND    ap.STUDENT_ID = '$student_id'
								    AND    ap.SCHOOL_DATE 
									          BETWEEN '$mp_start' and '$mp_end'
							     ) > 0
					 AND        ac.SYEAR = '$syear'
					 AND        ac.SCHOOL_ID = '$school_id'
					 AND        ac.TABLE_NAME = '0'
					 AND (      ac.DEFAULT_CODE != 'Y'
					 OR         ac.DEFAULT_CODE IS NULL )
					"));
					
			if (count($attendance_RET)) {
				$student_html .= '  
						<tr bgcolor="'.$bg_color.'">
							<td>&nbsp;</td>
							<td>&nbsp;</td>
							<td colspan="2"><em>Class Attendance Record:</em></td>';
						
				$iteration = 1;
				foreach ($attendance_RET as $attendance_ROW) {
					if ($iteration > 1) {
						$student_html .= ' 
						<tr bgcolor="' . $bg_color . '">
							<td>&nbsp;</td>
							<td>&nbsp;</td>
							<td>&nbsp;</td>
							<td>&nbsp;</td>';
					}
					$student_html .= '  
							<td colspan="3">'
								. $attendance_ROW['TITLE'] . ': '
								. $attendance_ROW['COUNT'] . '
							</td>
						</tr>';
							
					$iteration++;
				}
			}
			
			unset($attendance_RET);
			
			$student_html .= '
						<tr bgcolor="' . $bg_color . '">
							<td>&nbsp;</td>
						</tr>';
		}
		
		$student_html .= '
					</table>
							';
		if ($include_student) {
			echo $student_html;
		}
		
		// That's the end of all the PDF stuff for this page
		PDFStop($handle);
	}
	else
		BackPrompt(_('No Students were found.'));
}

function _makeExtra($value, $column)
{	global $THIS_RET
	,      $student_points
	,      $total_points
	,      $nonweighted_student_points
	,      $nonweighted_total_points
	,      $_FOCUS
	,      $is_weighted
	;
	
	if ($column == 'POINTS') {
		if ($value == '-1') {
			return '
					<table border="0" 
							cellspacing="0" 
							cellpadding="0" 
							class="innerLO_field">
						<tr>
							<td>
								<font size="-1">
									'._('Excluded').'
								</font>
							</td>
							<td></td>
							<td></td>
						</tr>
					</table>';
		}
		else {
			if ($is_weighted !== 'Y') {
				$THIS_RET['FINAL_GRADE_PERCENT'] = 1;
			}
			
			$total_points[$THIS_RET['TYPE_ID']] += $THIS_RET['TOTAL_POINTS'];	
			$nonweighted_student_points += $value;
			$nonweighted_total_points += $THIS_RET['TOTAL_POINTS'];
			$student_points[$THIS_RET['TYPE_ID']]  
					+= $value * $THIS_RET['FINAL_GRADE_PERCENT'];

			return '
					<table border="0" 
							cellspacing="0" 
							cellpadding="0" 
							class="innerLO_field">
						<tr>
							<td>'.($THIS_RET['POINTS']*1).'</td>
							<td>&nbsp;/&nbsp;</td>
							<td>'.$THIS_RET['TOTAL_POINTS'].'</td>
						</tr>
					</table>';
		}
	}
	elseif ($column == 'LETTER_GRADE') {
		if ($THIS_RET['POINTS'] == '-1') {
			return _('N/A') . '&nbsp;' . _('N/A');
		}
		elseif ($THIS_RET['TOTAL_POINTS'] == 0) {
			return _('EC');
		}
		else {
			return Percent($THIS_RET['POINTS'] / $THIS_RET['TOTAL_POINTS'], 0) 
				. '&nbsp;' 
				. _makeLetterGrade($THIS_RET['POINTS'] / $THIS_RET['TOTAL_POINTS']
						, $THIS_RET['COURSE_PERIOD_ID']
				);
		}
	}
}

function _removeSpaces($value, $column)
{
	if ($column == 'ASSIGNED_DATE') {
		$value = ProperDate($value);
	}
	
	return str_replace(' ', '&nbsp;', str_replace('&', '&amp;', $value));
}

function _makeLetterGrade($percent, $course_period_id, $teacher_username, $grade_scale_id)
{	global $programconfig
	,      $_FOCUS
	,      $user_syear
	,      $user_school
	;

	if (!$grade_scale_id) {
		$grade_scale_id = DBGet(DBQuery("
				SELECT       cp.COURSE_ID
				 ,           cp.TITLE
				 ,           c.TITLE
				 ,           cw.CREDITS
				 ,           cw.GPA_MULTIPLIER AS COURSE_TITLE
				 ,           cp.DOES_GRADES
				 ,           cp.GRADE_SCALE_ID
				FROM         COURSE_PERIODS cp
				 ,           COURSES c
				 ,           COURSE_WEIGHTS cw
				WHERE        cw.COURSE_ID = cp.COURSE_ID 
				 AND         cw.COURSE_WEIGHT = cp.COURSE_WEIGHT
				 AND         c.COURSE_ID = cp.COURSE_ID 
				 AND         cp.COURSE_PERIOD_ID = '$course_period_id'
				"));
		$grade_scale_id = $grade_scale_id[1]['GRADE_SCALE_ID'];
	}

	if (!$programconfig) {
		$config_RET = DBGet(DBQuery("
				SELECT       cfg.TITLE
				 ,           cfg.VALUE 
				FROM         PROGRAM_USER_CONFIG cfg
				WHERE        cfg.USERNAME = '$teacher_username'
				 AND         cfg.PROGRAM = 'Gradebook'
				"), array(), array('TITLE'));
				
		if (count($config_RET)) {
			foreach ($config_RET as $title => $value) {
				$programconfig[$title] = $value[1]['VALUE'];
			}
		}
	}
	
	switch ($programconfig['ROUNDING']) {
		case 'UP': {
			$percent = ceil($percent * 100);
		} 	break;
		
		case 'DOWN': {
			$percent = floor($percent * 100);
		}
		default: {
			$percent = round($percent * 100, 0);
		}
	}
	
	if (!$_FOCUS['_makeLetterGrade']['grades']) {
		$_FOCUS['_makeLetterGrade']['grades'] = DBGet(DBQuery("
				SELECT       rcg.TITLE
				 ,           rcg.ID
				 ,           rcg.DEFAULT_BREAKOFF
				FROM         REPORT_CARD_GRADES rcg
				WHERE        rcg.SYEAR = '$user_syear'
				 AND         rcg.SCHOOL_ID = '$user_school'
				 AND         rcg.SCALE_ID = '$grade_scale_id' 
				ORDER BY     rcg.SORT_ORDER
				"));
	}
	
	//$grades = array('A+','A','A-','B+','B','B-','C+','C','C-','D+','D','D-','F');

	$custom = false;
	foreach ($_FOCUS['_makeLetterGrade']['grades'] as $grade) {
		if ($programconfig[$course_period_id . '-' . $grade['ID']]) {
			$custom = true;
			break;
		}
	}

	if (!$custom) {
		foreach ($_FOCUS['_makeLetterGrade']['grades'] as $grade) {
			$programconfig[$course_period_id . '-' . $grade['ID']] = 
					$grade['DEFAULT_BREAKOFF'];
		}
	}
	
	foreach ($_FOCUS['_makeLetterGrade']['grades'] as $grade) {
		if ($percent >= $programconfig[$course_period_id . '-' . $grade['ID']] 
		&&  is_numeric($programconfig[$course_period_id . '-' . $grade['ID']])
		){
			return $grade['TITLE'];
		}
	}
}
?>
